#!/usr/bin/env python3
# -*- coding: utf-8 -*-

"""
增强版数据库操作类
专门处理ON DUPLICATE KEY UPDATE语句的返回值问题
"""

import logging
from typing import Dict, List, Any, Tuple
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
from sqlalchemy.engine import Result

logger = logging.getLogger(__name__)

class EnhancedDatabaseManager:
    """增强版数据库管理器"""
    
    def __init__(self, db_manager):
        """基于现有db_manager初始化"""
        self.db_manager = db_manager
        self.session_factory = db_manager.session_factory
        
    def execute_insert_or_update(self, sql: str, params: tuple = None) -> Dict[str, Any]:
        """
        执行INSERT或UPDATE语句，返回详细的执行结果
        特别处理 ON DUPLICATE KEY UPDATE 语句
        
        Returns:
            {
                'success': bool,           # 是否执行成功
                'affected_rows': int,      # 影响的行数
                'operation': str,          # 操作类型: 'insert', 'update', 'no_change'
                'last_insert_id': int,     # 最后插入的ID（如果是插入）
                'message': str             # 操作描述
            }
        """
        session = self.session_factory()
        try:
            # 转换参数格式
            if params:
                param_dict = {}
                new_sql = sql
                for i, param in enumerate(params):
                    param_key = f"param_{i}"
                    param_dict[param_key] = param
                    new_sql = new_sql.replace('%s', f":{param_key}", 1)
            else:
                new_sql = sql
                param_dict = {}
            
            # logger.debug(f"执行SQL: {new_sql}")
            # logger.debug(f"参数: {param_dict}")
            
            # 执行SQL
            result = session.execute(text(new_sql), param_dict)
            
            # 获取影响的行数
            affected_rows = result.rowcount
            
            # 获取最后插入的ID（如果有）
            last_insert_id = None
            try:
                if hasattr(result, 'lastrowid') and result.lastrowid:
                    last_insert_id = result.lastrowid
            except:
                pass
            
            # 判断操作类型
            operation = self._determine_operation(sql, affected_rows, last_insert_id)
            
            # 提交事务
            session.commit()
            
            result_info = {
                'success': True,
                'affected_rows': affected_rows,
                'operation': operation,
                'last_insert_id': last_insert_id,
                'message': self._generate_message(operation, affected_rows)
            }
            
            logger.info(f"SQL执行成功: {result_info['message']}")
            return result_info
            
        except Exception as e:
            session.rollback()
            logger.error(f"SQL执行失败: {sql}, 错误: {e}")
            return {
                'success': False,
                'affected_rows': 0,
                'operation': 'error',
                'last_insert_id': None,
                'message': f'执行失败: {str(e)}'
            }
        finally:
            session.close()
    
    def _determine_operation(self, sql: str, affected_rows: int, last_insert_id: Any) -> str:
        """判断操作类型"""
        sql_upper = sql.upper()
        
        if 'ON DUPLICATE KEY UPDATE' in sql_upper:
            # ON DUPLICATE KEY UPDATE 语句
            if affected_rows == 1:
                return 'insert'  # 插入了新记录
            elif affected_rows == 2:
                return 'update'  # 更新了现有记录
            elif affected_rows == 0:
                return 'no_change'  # 数据没有变化
            else:
                return 'bulk_operation'  # 批量操作
        elif 'INSERT' in sql_upper:
            if affected_rows > 0:
                return 'insert'
            else:
                return 'no_change'
        elif 'UPDATE' in sql_upper:
            if affected_rows > 0:
                return 'update'
            else:
                return 'no_change'
        else:
            return 'unknown'
    
    def _generate_message(self, operation: str, affected_rows: int) -> str:
        """生成操作描述"""
        messages = {
            'insert': f'插入了新记录 (影响行数: {affected_rows})',
            'update': f'更新了现有记录 (影响行数: {affected_rows})', 
            'no_change': '数据没有变化',
            'bulk_operation': f'批量操作 (影响行数: {affected_rows})',
            'error': '操作失败',
            'unknown': f'未知操作 (影响行数: {affected_rows})'
        }
        return messages.get(operation, f'操作完成 (影响行数: {affected_rows})')
    
    def execute_query_with_result_info(self, sql: str, params: tuple = None) -> Tuple[List[Dict], Dict[str, Any]]:
        """
        执行查询并返回结果和操作信息
        
        Returns:
            Tuple[查询结果, 操作信息]
        """
        if any(keyword in sql.upper() for keyword in ['INSERT', 'UPDATE', 'DELETE']):
            # 数据修改操作
            operation_info = self.execute_insert_or_update(sql, params)
            return [], operation_info
        else:
            # 查询操作
            try:
                result = self.db_manager.execute_query(sql, params)
                operation_info = {
                    'success': True,
                    'affected_rows': len(result),
                    'operation': 'select',
                    'last_insert_id': None,
                    'message': f'查询返回 {len(result)} 条记录'
                }
                return result, operation_info
            except Exception as e:
                operation_info = {
                    'success': False,
                    'affected_rows': 0,
                    'operation': 'error',
                    'last_insert_id': None,
                    'message': f'查询失败: {str(e)}'
                }
                return [], operation_info
    
    def test_on_duplicate_key_update(self) -> bool:
        """测试ON DUPLICATE KEY UPDATE功能"""
        logger.info("测试 ON DUPLICATE KEY UPDATE 功能...")
        
        try:
            # 测试SQL
            test_sql = '''
                INSERT INTO lottery_results 
                (lottery_type, period, all_numbers, draw_date, prize_info, updated_at, sales_amount, pool_amount)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                ON DUPLICATE KEY UPDATE
                lottery_type = VALUES(lottery_type),
                period = VALUES(period),
                all_numbers = VALUES(all_numbers),
                draw_date = VALUES(draw_date),
                prize_info = VALUES(prize_info),
                updated_at = NOW(),
                sales_amount = VALUES(sales_amount),
                pool_amount = VALUES(pool_amount)
            '''
            
            test_params = (
                'test_enhanced',
                'TEST_ENHANCED_001',
                '["01", "02"]',
                '2024-01-01',
                '[]',
                '2024-01-01 12:00:00',
                '1000000',
                '2000000'
            )
            
            # 第一次执行 - 应该是插入
            logger.info("第一次执行 (应该插入)...")
            result1 = self.execute_insert_or_update(test_sql, test_params)
            logger.info(f"第一次结果: {result1}")
            
            # 第二次执行 - 应该是更新
            logger.info("第二次执行 (应该更新)...")
            result2 = self.execute_insert_or_update(test_sql, test_params)
            logger.info(f"第二次结果: {result2}")
            
            # 清理测试数据
            cleanup_sql = "DELETE FROM lottery_results WHERE lottery_type = 'test_enhanced'"
            cleanup_result = self.execute_insert_or_update(cleanup_sql)
            logger.info(f"清理结果: {cleanup_result}")
            
            # 验证结果
            success = (
                result1['success'] and result1['operation'] == 'insert' and
                result2['success'] and result2['operation'] == 'update' and
                cleanup_result['success']
            )
            
            if success:
                logger.info("✅ ON DUPLICATE KEY UPDATE 功能测试通过")
            else:
                logger.error("❌ ON DUPLICATE KEY UPDATE 功能测试失败")
            
            return success
            
        except Exception as e:
            logger.error(f"测试失败: {e}")
            return False

def create_enhanced_manager(db_manager):
    """创建增强版数据库管理器"""
    return EnhancedDatabaseManager(db_manager)
